package com.thghh.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.CharUtil;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.core.util.StrUtil;
import com.thghh.entity.KeyTypeEntity;
import com.thghh.entity.TableEntity;
import com.thghh.entity.TableFieldEntity;
import com.thghh.util.ExcelDataUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 根据excel中的表配置生成sql语句
 *
 * @author pengzhikang
 * @date 2021/12/15 15:42
 * @since 1.0.0
 */
@Slf4j
public class GenerateSqlService {

    /**
     * 生成sql
     *
     * @param filePath 文件路径
     * @return {@link List<String> }
     * @throws IOException ioexception
     * @author pengzhikang
     * @date 2023/06/08 10:23
     */
    public List<String> generateSql(String filePath) throws IOException {
        File file = new File(filePath);
        if (!file.exists()) {
            throw new FileNotFoundException(file.getPath() + " is not exists.");
        }
        InputStream input = new FileInputStream(filePath);
        return generateSql(input);
    }

    /**
     * 生成sql
     *
     * @param input 输入
     * @return {@link List<String> }
     * @throws IOException ioexception
     * @author pengzhikang
     * @date 2023/06/08 10:23
     */
    public List<String> generateSql(InputStream input) throws IOException {
        List<TableEntity> tableEntityList = readXls(input);
        processKeyType(tableEntityList);

        List<String> sqlList = new ArrayList<>(tableEntityList.size());
        for (TableEntity tableEntity : tableEntityList) {
            String sql = generateTableSQL(tableEntity);
            log.info("{}", sql);
            sqlList.add(sql);
        }
        return sqlList;
    }

    /**
     * 处理表主键设置
     * <pre>
     *     如：idx_prod_code:1
     *     表示此列存在一个索引，索引为idx_prod_code并且位置为1
     *     如：idx_effect_date,idx_prod_code:2
     *     表示此列存在两个索引，第一个索引为idx_effect_date，第二个索引为idx_prod_code并且位置为2
     * </pre>
     *
     * @param tableEntityList 表实体列表
     * @return {@link String }
     * @author pengzhikang
     * @date 2023/06/07 15:45
     */
    private void processKeyType(List<TableEntity> tableEntityList) {
        for (TableEntity tableEntity : tableEntityList) {
            Set<String> keyNameSet = new HashSet<>();
            tableEntity.setKeyNameSet(keyNameSet);

            List<TableFieldEntity> tableFieldEntityList = tableEntity.getFieldEntity();
            for (TableFieldEntity tableField : tableFieldEntityList) {
                String keyTypeName = tableField.getKeyTypeName();
                if (StrUtil.isBlank(keyTypeName)) {
                    continue;
                }
                if ("PK".equalsIgnoreCase(keyTypeName)) {
                    tableField.setPrimaryKey("Y");
                } else {
                    List<KeyTypeEntity> keyTypeEntityList = new ArrayList<>();
                    tableField.setKeyTypeList(keyTypeEntityList);

                    String[] idxArr = keyTypeName.split(",");
                    for (String idx : idxArr) {
                        String[] idxDetails = idx.split(":");
                        KeyTypeEntity keyTypeEntity = new KeyTypeEntity();
                        keyTypeEntity.setFieldName(tableField.getFieldName());
                        keyTypeEntity.setKeyName(idxDetails[0]);
                        if(idxDetails.length > 1) {
                            keyTypeEntity.setKeyNo(Integer.valueOf(idxDetails[1]));
                        } else {
                            keyTypeEntity.setKeyNo(1);
                        }
                        keyTypeEntity.setKeyType(idxDetails[0].startsWith("uk") || idxDetails[0].startsWith("UK") ? KeyTypeEntity.keyType.UK : KeyTypeEntity.keyType.IDX);
                        keyTypeEntityList.add(keyTypeEntity);

                        keyNameSet.add(idxDetails[0]);
                    }
                }
            }
        }
    }

    /**
     * 创建表sql
     *
     * @param tableEntity 表实体
     * @return {@link String }
     * @author pengzhikang
     * @date 2022/10/14 11:04
     */
    private String generateTableSQL(TableEntity tableEntity) {
        String tableName = tableEntity.getName();
        String tableComment = tableEntity.getComment();
        List<TableFieldEntity> tableFieldEntityList = tableEntity.getFieldEntity();

        StringBuilder sql = new StringBuilder();

        sql.append("-- ").append(tableComment).append("\n");
        String dropTableSQL = String.format("DROP TABLE IF EXISTS %s;", tableName);
        sql.append(dropTableSQL).append("\n");

        sql.append("CREATE TABLE ").append(tableName).append(" (").append("\n");
        String primaryKey = null;
        for (int i = 0; i < tableFieldEntityList.size(); i++) {
            TableFieldEntity tableField = tableFieldEntityList.get(i);
            String fieldSql = String.format("    %s %s", tableField.getFieldName(), tableField.getFieldType().toUpperCase(Locale.ROOT));
            sql.append(fieldSql);
            sql.append(" ");
            // 是否为空
            if ("Y".equals(tableField.getIsNull()) || "y".equals(tableField.getIsNull()) || StrUtil.isBlank(tableField.getIsNull())) {
                // 默认值
                if(StrUtil.isNotBlank(tableField.getDefaultValue())) {
                    sql.append("DEFAULT ").append(tableField.getDefaultValue()).append(" ");
                } else {
                    sql.append("DEFAULT NULL").append(" ");
                }
            } else if ("N".equals(tableField.getIsNull()) || "n".equals(tableField.getIsNull())) {
                sql.append("NOT NULL").append(" ");
                if(StrUtil.isNotBlank(tableField.getDefaultValue())) {
                    if (tableField.getPrimaryKey() != null && "Y,y,是,YES,yes".contains(tableField.getPrimaryKey())) {
                        sql.append(tableField.getDefaultValue()).append(" ");
                    } else {
                        sql.append("DEFAULT ").append(tableField.getDefaultValue()).append(" ");
                    }
                }
            }

            // 注释
            sql.append("COMMENT '").append(tableField.getComment().trim()).append("'").append(",").append("\n");

            if (tableField.getPrimaryKey() != null && "Y,y,是,YES,yes".contains(tableField.getPrimaryKey())) {
                primaryKey = String.format("    PRIMARY KEY (%s)", tableField.getFieldName());
            }
        }
        sql.append(primaryKey);
        // 索引的设置
        Set<String> keyNameSet = tableEntity.getKeyNameSet();
        if(CollUtil.isNotEmpty(keyNameSet)) {
            sql.append(",\n");
            Iterator<String> iterator = keyNameSet.iterator();
            while (iterator.hasNext()) {
                String keyName = iterator.next();
                List<KeyTypeEntity> generateKeyList = tableFieldEntityList.stream().filter(p -> Objects.nonNull(p.getKeyTypeList()))
                        .map(p -> p.getKeyTypeList().stream().filter(k -> keyName.equals(k.getKeyName())).findFirst().orElse(null))
                        .filter(Objects::nonNull).sorted(Comparator.comparing(KeyTypeEntity::getKeyNo)).collect(Collectors.toList());

                String keyType = generateKeyList.get(0).getKeyType() == KeyTypeEntity.keyType.UK ? "UNIQUE KEY" : "KEY";
                String format = String.format("    %s %s (%s)", keyType, keyName, generateKeyList.stream().map(KeyTypeEntity::getFieldName).collect(Collectors.joining(", ")));
                sql.append(format);
                if (iterator.hasNext()) {
                    sql.append(",\n");
                }
            }
        }
        sql.append("\n");
        sql.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='").append(tableComment).append("';\n\n");

        return sql.toString();
    }



    /**
     * 读xls
     *
     * @param filePath 文件路径
     * @return {@link List<TableFieldEntity> }
     * @throws IOException e
     * @author pengzhikang
     * @date 2021/12/15 15:58
     */
    public List<TableEntity> readXls(InputStream stream) throws IOException {
        List<TableEntity> tableEntityList = new ArrayList<>();
        try (InputStream is = new BufferedInputStream(stream)) {
            XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }

                String tableName = ExcelDataUtils.getStringValue(hssfSheet.getRow(0), 7);
                String tableComment = hssfSheet.getSheetName();
                List<TableFieldEntity> tableFieldEntityList = new ArrayList<>();

                TableEntity tableEntity = new TableEntity();
                tableEntity.setName(tableName);
                tableEntity.setComment(tableComment);
                tableEntity.setFieldEntity(tableFieldEntityList);
                tableEntityList.add(tableEntity);

                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        break;
                    }
                    String isEmpty = ExcelDataUtils.getStringValue(hssfRow, 0);
                    if (StringUtils.isBlank(isEmpty)) {
                        break;
                    }
                    TableFieldEntity fieldEntity = new TableFieldEntity();
                    fieldEntity.setId(ExcelDataUtils.getIntValue(hssfRow, 0));
                    fieldEntity.setFieldName(ExcelDataUtils.getStringValue(hssfRow, 1));
                    fieldEntity.setFieldType(ExcelDataUtils.getStringValue(hssfRow, 2));
                    fieldEntity.setIsNull(ExcelDataUtils.getStringValue(hssfRow, 3));
                    fieldEntity.setDefaultValue(ExcelDataUtils.getStringValue(hssfRow, 4));
                    fieldEntity.setKeyTypeName(ExcelDataUtils.getStringValue(hssfRow, 5));
                    fieldEntity.setComment(ExcelDataUtils.getStringValue(hssfRow, 6));
                    tableFieldEntityList.add(fieldEntity);
                }
            }
        }
        return tableEntityList;
    }

    private String aiValue(Row row, int column) {
        Object obj = ExcelDataUtils.getValue(row.getCell(column));
        if (obj instanceof Double) {
            Double d = (Double) obj;
            return String.valueOf(d.intValue());
        }
        return String.valueOf(obj);
    }

}
